1 Public Class Frm_Employee
2
3 Dim emp_id
4
5 Function emptyFields()
6
7 If txtName.Text = "" OrElse txtAge.Text = "" OrElse txtEmail.Text = "" OrElse txtAddress.Text = "" OrElse txtSalary.Text = "" Then
8
9 Return True
10
11 End If
12
13 Return False
14
15 End Function
16
17 Sub formClear()
18
19 txtName.Clear()
20 txtAge.Clear()
21 txtEmail.Clear()
22 txtPhone.Clear()
23 txtAddress.Clear()
24 txtSalary.Clear()
25
26 End Sub
27
28
29 Private Sub add()
30
31 If emptyFields() = True Then
32
33 MsgBox("Must Enter In All Fields ", MsgBoxStyle.Information)
34
35 Return
36
37 Else
38
39 dgvEmployee.Rows.Add()
40 dgvEmployee.Rows(dgvEmployee.RowCount - 1).Cells("EmpID").Value = txtID.Text
41 dgvEmployee.Rows(dgvEmployee.RowCount - 1).Cells("EmpName").Value = txtName.Text
42 dgvEmployee.Rows(dgvEmployee.RowCount - 1).Cells("EmpAge").Value = txtAge.Text
43 dgvEmployee.Rows(dgvEmployee.RowCount - 1).Cells("EmpEmail").Value = txtEmail.Text
44 dgvEmployee.Rows(dgvEmployee.RowCount - 1).Cells("EmpPhone").Value = txtPhone.Text
45 dgvEmployee.Rows(dgvEmployee.RowCount - 1).Cells("EmpAddress").Value = txtAddress.Text
46 dgvEmployee.Rows(dgvEmployee.RowCount - 1).Cells("EmpSalary").Value = txtSalary.Text
47 dgvEmployee.Rows(dgvEmployee.RowCount - 1).Cells("EmpDepttID").Value = cmboxDeptt.SelectedValue
48 dgvEmployee.Rows(dgvEmployee.RowCount - 1).Cells("EmpDeptt").Value = cmboxDeptt.Text
49 dgvEmployee.Rows(dgvEmployee.RowCount - 1).DefaultCellStyle.ForeColor = Color.Blue
50
51 End If
52
53 formClear()
54
55 End Sub
56
57 Private Sub edit()
58
59 If dgvEmployee.SelectedRows.Count = 0 Then
60
61 Return
62
63 End If
64
65 If btnEdit.Text = "Edit" Then
66
67 txtID.Text = dgvEmployee.SelectedRows(0).Cells("EmpID").Value
68 txtName.Text = dgvEmployee.SelectedRows(0).Cells("EmpName").Value
69 txtAge.Text = dgvEmployee.SelectedRows(0).Cells("EmpAge").Value
70 txtEmail.Text = dgvEmployee.SelectedRows(0).Cells("EmpEmail").Value
71 txtPhone.Text = dgvEmployee.SelectedRows(0).Cells("EmpPhone").Value
72 txtAddress.Text = dgvEmployee.SelectedRows(0).Cells("EmpAddress").Value
73 txtSalary.Text = dgvEmployee.SelectedRows(0).Cells("EmpSalary").Value
74 cmboxDeptt.SelectedValue = dgvEmployee.SelectedRows(0).Cells("EmpDepttID").Value
75 cmboxDeptt.Text = dgvEmployee.SelectedRows(0).Cells("EmpDeptt").Value
76 dgvEmployee.Enabled = False
77 btnEdit.Text = "Update"
78
79 Else
80
81 dgvEmployee.SelectedRows(0).Cells("EmpID").Value = txtID.Text
82 dgvEmployee.SelectedRows(0).Cells("EmpName").Value = txtName.Text
83 dgvEmployee.SelectedRows(0).Cells("EmpAge").Value = txtAge.Text
84 dgvEmployee.SelectedRows(0).Cells("EmpEmail").Value = txtEmail.Text
85 dgvEmployee.SelectedRows(0).Cells("EmpPhone").Value = txtPhone.Text
86 dgvEmployee.SelectedRows(0).Cells("EmpAddress").Value = txtAddress.Text
87 dgvEmployee.SelectedRows(0).Cells("EmpSalary").Value = txtSalary.Text
88 dgvEmployee.SelectedRows(0).Cells("EmpDepttID").Value = cmboxDeptt.SelectedValue
89 dgvEmployee.SelectedRows(0).Cells("EmpDeptt").Value = cmboxDeptt.Text
90 dgvEmployee.Enabled = True
91 btnEdit.Text = "Edit"
92
93
94 End If
95
96 dgvEmployee.SelectedRows(0).DefaultCellStyle.ForeColor = Color.Blue
97
98 End Sub
99
100 Private Sub remove()
101
102 If dgvEmployee.SelectedRows.Count = 0 Then
103
104 Return
105
106 End If
107
108 dgvEmployee.SelectedRows(0).DefaultCellStyle.ForeColor = Color.Red
109
110 End Sub
111
112 Private Sub apply()
113
114 Dim query As String = String.Empty
115
116 For Each row As DataGridViewRow In dgvEmployee.Rows
117
118 If row.Cells("FromDB").Value = True Then
119
120 If row.DefaultCellStyle.ForeColor = Color.Black Then
121
122 Continue For
123
124 ElseIf row.DefaultCellStyle.ForeColor = Color.Blue Then
125
126 query = "Update Employee set full_name = '" & row.Cells("EmpName").Value & "',age = '" & row.Cells("EmpAge").Value & "',email = '" & row.Cells("EmpEmail").Value & "',phone_number = '" & row.Cells("EmpPhone").Value & "',address = '" & row.Cells("EmpAddress").Value & "',salary = '" & row.Cells("EmpSalary").Value & "', department_id = '" & row.Cells("EmpDepttID").Value & "' Where employee_id = '" & row.Cells("EmpID").Value & "'"
127
128 ElseIf row.DefaultCellStyle.ForeColor = Color.Red Then
129
130 query = "Delete From Employee Where employee_id = " & row.Cells("EmpID").Value
131
132 End If
133
134
135 Else
136
137 If row.DefaultCellStyle.ForeColor = Color.Blue Then
138
139 query = "Insert Into Employee (full_name,age,email,phone_number,address,salary,department_id) Values ('" & row.Cells("EmpName").Value & "','" & row.Cells("EmpAge").Value & "','" & row.Cells("EmpEmail").Value & "','" & row.Cells("EmpPhone").Value & "','" & row.Cells("EmpAddress").Value & "','" & row.Cells("EmpSalary").Value & "','" & row.Cells("EmpDepttID").Value & "')"
140
141 ElseIf row.DefaultCellStyle.ForeColor = Color.Red Then
142
143 dgvEmployee.Rows.Remove(row)
144
145 Continue For
146
147 End If
148
149 End If
150
151 executeQuery(query)
152
153 Next
154
155 reload()
156
157 End Sub
158
159 Private Sub reload()
160
161 Dim query As String
162 query = "Select E.*, D.department_name, D.department_id"
163 query = query + " From Employee E "
164 query = query + " Inner Join Department D On E.department_id = D.department_id"
165
166 dgvEmployee.Rows.Clear()
167
168 Dim dt As DataTable = getDataTable(query)
169
170 For Each row As DataRow In dt.Rows
171
172 dgvEmployee.Rows.Add()
173 dgvEmployee.Rows(dgvEmployee.RowCount - 1).Cells("EmpID").Value = row("employee_id")
174 dgvEmployee.Rows(dgvEmployee.RowCount - 1).Cells("EmpName").Value = row("full_name")
175 dgvEmployee.Rows(dgvEmployee.RowCount - 1).Cells("EmpAge").Value = row("age")
176 dgvEmployee.Rows(dgvEmployee.RowCount - 1).Cells("EmpEmail").Value = row("email")
177 dgvEmployee.Rows(dgvEmployee.RowCount - 1).Cells("EmpPhone").Value = row("phone_number")
178 dgvEmployee.Rows(dgvEmployee.RowCount - 1).Cells("EmpAddress").Value = row("address")
179 dgvEmployee.Rows(dgvEmployee.RowCount - 1).Cells("EmpSalary").Value = row("salary")
180 dgvEmployee.Rows(dgvEmployee.RowCount - 1).Cells("EmpDepttID").Value = row("department_id")
181 dgvEmployee.Rows(dgvEmployee.RowCount - 1).Cells("EmpDeptt").Value = row("department_name")
182 dgvEmployee.Rows(dgvEmployee.RowCount - 1).Cells("FromDB").Value = True
183 dgvEmployee.Rows(dgvEmployee.RowCount - 1).DefaultCellStyle.ForeColor = Color.Black
184
185 Next
186
187 txtID.Text = autoId().ToString()
188
189 End Sub
190
191 Private Sub fillCmboxDepartment() ' for getting deparment names from database
192
193 Dim query As String = "Select department_id, department_name From Department order by department_name"
194 Dim dtDeptt As DataTable = getDataTable(query)
195
196 cmboxDeptt.DataSource = dtDeptt
197 cmboxDeptt.DisplayMember = "department_name"
198 cmboxDeptt.ValueMember = "department_id"
199
200 If cmboxDeptt.Items.Count > 0 Then
201 cmboxDeptt.SelectedIndex = 0
202 End If
203
204 End Sub
205
206 Private Function autoId() As Integer ' generating auto employee id for a new employee
207
208 Dim query = "Select IsNull(Max(employee_id+1),0) employee_id From Employee"
209 Dim dr As SqlClient.SqlDataReader
210 dr = getDataReader(query)
211 dr.Read()
212 emp_id = dr("employee_id")
213 dr.Close()
214
215 Return emp_id
216
217 End Function
218
219 Private Sub Frm_Employee_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
220 Try
221 fillCmboxDepartment() ' loading all department names from database to combobox department
222 loadAllEmployees() ' loading all employee names from database to combobox employee for searching purpose
223 loadAllJobTitles() ' loading all job titles from database to combobox job titles
224 reload()
225 txtID.Text = autoId().ToString()
226 dateTimeEnd.MinDate = Now
227 dateTimeJoin.MinDate = Now
228 Catch ex As Exception
229 MsgBox(ex.Message, MsgBoxStyle.Critical)
230 End Try
231 End Sub
232
233 Private Sub loadAllEmployees()
234
235 Dim query As String = "Select employee_id, full_name From Employee order by full_name"
236 Dim dtEmp As DataTable = getDataTable(query)
237
238 cmboxNames.DataSource = dtEmp
239 cmboxNames.DisplayMember = "full_name"
240 cmboxNames.ValueMember = "employee_id"
241
242 If cmboxNames.Items.Count > 0 Then
243 cmboxNames.SelectedIndex = 0
244 End If
245
246 End Sub
247
248 Private Sub lstboxNames_DoubleClick(ByVal sender As System.Object, ByVal e As System.EventArgs)
249
250 Dim name = lstboxNames.SelectedItem.ToString()
251
252 Dim query As IEnumerable(Of DataGridViewRow) = _
253 From row As DataGridViewRow In dgvEmployee.Rows _
254 Where row.Cells("EmpName").Value.ToString().Contains(name) _
255 Select row
256
257 For Each row As DataGridViewRow In query
258 row.Selected = True
259 Next
260
261 End Sub
262
263 Private Sub loadAllJobTitles()
264
265 Dim query As String = "Select job_id, job_title From Employee_Job_Info order by job_title"
266 Dim dtEmp As DataTable = getDataTable(query)
267
268 cmboxTitle.DataSource = dtEmp
269 cmboxTitle.DisplayMember = "job_title"
270 cmboxTitle.ValueMember = "job_id"
271
272 If cmboxTitle.Items.Count > 0 Then
273 cmboxTitle.SelectedIndex = 0
274 End If
275
276 End Sub
277
278 Private Sub btnAddJob_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddJob.Click
279 Try
280 add()
281 apply()
282 Dim query = "Insert Into Employee_Job_History(employee_id,start_date,end_date,job_id,department_id) Values ('" & Convert.ToInt32(emp_id - 1) & "','" & dateTimeJoin.Value & "','" & dateTimeEnd.Value & "','" & cmboxTitle.SelectedValue & "','" & cmboxDeptt.SelectedValue & "')"
283 executeQuery(query)
284 MsgBox("Employee And Job Added Successfully")
285 TabControl1.TabPages("Employee Details").Show()
286 Catch ex As Exception
287 MsgBox(ex.Message, MsgBoxStyle.Critical)
288 End Try
289 End Sub
290
291 Private Sub btnAdd_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
292 Try
293 add()
294 Catch ex As Exception
295 MsgBox(ex.Message, MsgBoxStyle.Critical)
296 End Try
297 End Sub
298
299 Private Sub btnEdit_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
300 Try
301 edit()
302 Catch ex As Exception
303 MsgBox(ex.Message, MsgBoxStyle.Critical)
304 End Try
305 End Sub
306
307 Private Sub btnRemove_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRemove.Click
308 Try
309 remove()
310 Catch ex As Exception
311 MsgBox(ex.Message, MsgBoxStyle.Critical)
312 End Try
313 End Sub
314
315 Private Sub btnApply_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnApply.Click
316 Try
317 apply()
318 Catch ex As Exception
319 MsgBox(ex.Message, MsgBoxStyle.Critical)
320 End Try
321 End Sub
322
323 Private Sub btnOk_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOk.Click
324 Try
325 apply()
326 Me.Close()
327 Catch ex As Exception
328 MsgBox(ex.Message, MsgBoxStyle.Critical)
329 End Try
330 End Sub
331
332 Private Sub btnCancel_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
333 Me.Close()
334 End Sub
335
336 Private Sub btnSubmit_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
337 lstboxNames.Items.Add(cmboxNames.Text)
338 End Sub
339
340 Private Sub cmboxNames_SelectedIndexChanged_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmboxNames.SelectedIndexChanged
341
342 Dim name = cmboxNames.Text
343
344 Dim query As IEnumerable(Of DataGridViewRow) = _
345 From row As DataGridViewRow In dgvEmployee.Rows _
346 Where row.Cells("EmpName").Value.ToString().Contains(name) _
347 Select row
348
349 For Each row As DataGridViewRow In query
350 row.Selected = True
351 Next
352 End Sub
353
354 Private Sub txtName_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtName.KeyPress
355 Dim ch As Char = e.KeyChar
356 If Char.IsDigit(ch) Then 'Ristricting TxtName To Input Only Characters
357 e.Handled = True
358 End If
359 End Sub
360
361 Private Sub txtAge_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtAge.KeyPress
362 Dim ch As Char = e.KeyChar
363 If Char.IsLetter(ch) Then 'Ristricting age To Input Only Digits(any number)
364 e.Handled = True
365 End If
366 End Sub
367
368 Private Sub txtPhone_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtPhone.KeyPress
369 Dim ch As Char = e.KeyChar
370 If Char.IsLetter(ch) Then 'Ristricting TxtPhone To Input Only Digits(any number)
371 e.Handled = True
372 End If
373 End Sub
374
375 Private Sub txtSalary_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtSalary.KeyPress
376 Dim ch As Char = e.KeyChar
377 If Char.IsLetter(ch) Then 'Ristricting salary To Input Only Digits(any number)
378 e.Handled = True
379 End If
380 End Sub
381 End Class
